package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.*;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.wms.WMSBatchPickDemand;
import com.iplant.aps.service.po.wms.WMSLineEdgeLibrary;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.MessageFormat;
import java.util.*;
import java.util.stream.Collectors;

public class WMSBatchPickDemandDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(WMSBatchPickDemandDAO.class);

    private static WMSBatchPickDemandDAO Instance = null;

    private WMSBatchPickDemandDAO() {
        super();
    }

    public static WMSBatchPickDemandDAO getInstance() {
        if (Instance == null)
            Instance = new WMSBatchPickDemandDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, WMSBatchPickDemand wWMSBatchPickDemand, OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wWMSBatchPickDemand == null)
                return 0;

            String wSQL = "";
            if (wWMSBatchPickDemand.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.wms_batchpickdemand(Factory,OrderType,DemandNo," +
                                "ExpectTime1,ExpectTime2,ProductID,LineID,SorterID,SorterTime,DeliveryID," +
                                "DeliveryTime," +
                                "ReceiveID,ReceiveTime,Status,SendStatus,SendDesc,CreateID,CreateTime,WMSDemandNo," +
                                "Remark,SourceType,SourceID,SourceCode,ShortageIdentification,OriginalOrderNo," +
                                "ParentDemandNo) VALUES" +
                                "(:Factory,:OrderType,:DemandNo,:ExpectTime1,:ExpectTime2,:ProductID,:LineID," +
                                ":SorterID,:SorterTime,:DeliveryID,:DeliveryTime,:ReceiveID,:ReceiveTime,:Status," +
                                ":SendStatus,:SendDesc,:CreateID,:CreateTime,:WMSDemandNo,:Remark,:SourceType," +
                                ":SourceID,:SourceCode,:ShortageIdentification,:OriginalOrderNo,:ParentDemandNo);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.wms_batchpickdemand SET Factory = :Factory,OrderType = " +
                                ":OrderType,DemandNo = :DemandNo,ExpectTime1 = :ExpectTime1,ExpectTime2 = " +
                                ":ExpectTime2,ProductID = :ProductID,LineID = :LineID,SorterID = :SorterID,SorterTime" +
                                " " +
                                "= :SorterTime,DeliveryID = :DeliveryID,DeliveryTime = :DeliveryTime,ReceiveID = " +
                                ":ReceiveID,ReceiveTime = :ReceiveTime,Status = :Status,SendStatus = :SendStatus," +
                                "SendDesc = :SendDesc,CreateID = :CreateID,CreateTime = :CreateTime,WMSDemandNo = " +
                                ":WMSDemandNo,Remark = :Remark,SourceType = :SourceType,SourceID = :SourceID," +
                                "SourceCode = :SourceCode,ShortageIdentification = :ShortageIdentification," +
                                "OriginalOrderNo = :OriginalOrderNo,ParentDemandNo=:ParentDemandNo WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wWMSBatchPickDemand.ID);
            wParamMap.put("Factory", wWMSBatchPickDemand.Factory);
            wParamMap.put("OrderType", wWMSBatchPickDemand.OrderType);
            wParamMap.put("DemandNo", wWMSBatchPickDemand.DemandNo);
            wParamMap.put("ExpectTime1", wWMSBatchPickDemand.ExpectTime1);
            wParamMap.put("ExpectTime2", wWMSBatchPickDemand.ExpectTime2);
            wParamMap.put("ProductID", wWMSBatchPickDemand.ProductID);
            wParamMap.put("LineID", wWMSBatchPickDemand.LineID);
            wParamMap.put("SorterID", wWMSBatchPickDemand.SorterID);
            wParamMap.put("SorterTime", wWMSBatchPickDemand.SorterTime);
            wParamMap.put("DeliveryID", wWMSBatchPickDemand.DeliveryID);
            wParamMap.put("DeliveryTime", wWMSBatchPickDemand.DeliveryTime);
            wParamMap.put("ReceiveID", wWMSBatchPickDemand.ReceiveID);
            wParamMap.put("ReceiveTime", wWMSBatchPickDemand.ReceiveTime);
            wParamMap.put("Status", wWMSBatchPickDemand.Status);
            wParamMap.put("SendStatus", wWMSBatchPickDemand.SendStatus);
            wParamMap.put("SendDesc", wWMSBatchPickDemand.SendDesc);
            wParamMap.put("CreateID", wWMSBatchPickDemand.CreateID);
            wParamMap.put("CreateTime", wWMSBatchPickDemand.CreateTime);
            wParamMap.put("WMSDemandNo", wWMSBatchPickDemand.WMSDemandNo);
            wParamMap.put("Remark", wWMSBatchPickDemand.Remark);
            wParamMap.put("SourceType", wWMSBatchPickDemand.SourceType);
            wParamMap.put("SourceID", wWMSBatchPickDemand.SourceID);
            wParamMap.put("SourceCode", wWMSBatchPickDemand.SourceCode);
            wParamMap.put("ShortageIdentification", wWMSBatchPickDemand.ShortageIdentification);
            wParamMap.put("OriginalOrderNo", wWMSBatchPickDemand.OriginalOrderNo);
            wParamMap.put("ParentDemandNo", wWMSBatchPickDemand.ParentDemandNo);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wWMSBatchPickDemand.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wWMSBatchPickDemand.setID(wResult);
            } else {
                wResult = wWMSBatchPickDemand.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<WMSBatchPickDemand> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (WMSBatchPickDemand wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.wms_batchpickdemand WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSBatchPickDemand SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSBatchPickDemand wResult = new WMSBatchPickDemand();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSBatchPickDemand> wList = SelectList(wLoginUser, wID, "", -1, -1, -1, "", "", -1, "", null, null,
                    wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
            wResult.ItemList = WMSBatchPickDemandItemDAO.getInstance().SelectList(wLoginUser, -1, wResult.ID, "",
                    -1, "", "", "", "", -1, wErrorCode);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<WMSBatchPickDemand> SelectList(BMSEmployee wLoginUser, int wID, String wDemandNo, int wProductID,
                                               int wLineID, int wStatus, String wWMSDemandNo, String wSourceCode,
                                               int wShortageIdentification, String wOriginalOrderNo,
                                               Calendar wStartTime, Calendar wEndTime, OutResult<Integer> wErrorCode) {
        List<WMSBatchPickDemand> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.ProductNo,t2.Name LineName,t3.Name Sorter," +
                            "t3.LoginID SorterNo,t4.Name DeliveryMan,t4.LoginID DeliveryNo," +
                            "t5.Name ReceiveMan,t5.LoginID ReceiveNo,t6.Name Creator FROM {0}" +
                            ".wms_batchpickdemand t "
                            + " left join {0}.fpc_product t1 on t.ProductID=t1.ID "
                            + " left join {0}.fmc_line t2 on t.LineID=t2.ID "
                            + " left join {0}.mbs_user t3 on t.SorterID=t3.ID "
                            + " left join {0}.mbs_user t4 on t.DeliveryID=t4.ID "
                            + " left join {0}.mbs_user t5 on t.ReceiveID=t5.ID "
                            + " left join {0}.mbs_user t6 on t.CreateID=t6.ID "
                            + "WHERE  1=1  and ( :wID <= 0 or :wID = t.ID ) and ( " +
                            ":wDemandNo is null or :wDemandNo = '''' or :wDemandNo = t.DemandNo ) and ( :wProductID " +
                            "<= " +
                            "0" +
                            " or :wProductID = t.ProductID ) and ( :wLineID <= 0 or :wLineID = t.LineID ) and ( " +
                            ":wStatus " +
                            "<= 0 or :wStatus = t.Status )and ( :wStartTime <=str_to_date(''2010-01-01'', " +
                            "''%Y-%m-%d'')" +
                            " " +
                            " or :wStartTime <=  t.CreateTime ) and ( :wEndTime <=str_to_date(''2010-01-01'', " +
                            "''%Y-%m-%d'')  or :wEndTime >=  t.CreateTime ) and ( :wWMSDemandNo is null or " +
                            ":wWMSDemandNo" +
                            " = '''' or :wWMSDemandNo = t.WMSDemandNo ) and ( :wSourceCode is null or :wSourceCode = " +
                            "'''' or :wSourceCode = t.SourceCode ) and ( :wShortageIdentification <= 0 or " +
                            ":wShortageIdentification = t.ShortageIdentification ) and ( :wOriginalOrderNo is null or" +
                            " " +
                            ":wOriginalOrderNo = '''' or :wOriginalOrderNo = t.OriginalOrderNo );",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wDemandNo", wDemandNo);
            wParamMap.put("wProductID", wProductID);
            wParamMap.put("wLineID", wLineID);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wWMSDemandNo", wWMSDemandNo);
            wParamMap.put("wSourceCode", wSourceCode);
            wParamMap.put("wShortageIdentification", wShortageIdentification);
            wParamMap.put("wOriginalOrderNo", wOriginalOrderNo);
            wParamMap.put("wStartTime", wStartTime);
            wParamMap.put("wEndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SetValue(wResultList, wQueryResult);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 赋值操作
     */
    private void SetValue(List<WMSBatchPickDemand> wResultList, List<Map<String, Object>> wQueryResult) {
        try {
            for (Map<String, Object> wReader : wQueryResult) {
                WMSBatchPickDemand wItem = new WMSBatchPickDemand();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.Factory = StringUtils.parseString(wReader.get("Factory"));
                wItem.OrderType = StringUtils.parseString(wReader.get("OrderType"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.ExpectTime1 = StringUtils.parseCalendar(wReader.get("ExpectTime1"));
                wItem.ExpectTime2 = StringUtils.parseCalendar(wReader.get("ExpectTime2"));
                wItem.ProductID = StringUtils.parseInt(wReader.get("ProductID"));
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.LineID = StringUtils.parseInt(wReader.get("LineID"));
                wItem.LineName = StringUtils.parseString(wReader.get("LineName"));
                wItem.SorterID = StringUtils.parseInt(wReader.get("SorterID"));
                wItem.Sorter = StringUtils.parseString(wReader.get("Sorter"));
                wItem.SorterNo = StringUtils.parseString(wReader.get("SorterNo"));
                wItem.SorterTime = StringUtils.parseCalendar(wReader.get("SorterTime"));
                wItem.DeliveryID = StringUtils.parseInt(wReader.get("DeliveryID"));
                wItem.DeliveryMan = StringUtils.parseString(wReader.get("DeliveryMan"));
                if (wItem.DeliveryID == -100)
                    wItem.DeliveryMan = "WMS系统";
                wItem.DeliveryNo = StringUtils.parseString(wReader.get("DeliveryNo"));
                wItem.DeliveryTime = StringUtils.parseCalendar(wReader.get("DeliveryTime"));

                Calendar wBaseTime = Calendar.getInstance();
                wBaseTime.set(2010, 0, 1, 0, 0, 0);
                if (wItem.DeliveryTime.compareTo(wBaseTime) <= 0) {
                    wItem.DeliveryMan = "";
                    wItem.DeliveryNo = "";
                }

                wItem.ReceiveID = StringUtils.parseInt(wReader.get("ReceiveID"));
                wItem.ReceiveMan = StringUtils.parseString(wReader.get("ReceiveMan"));
                wItem.ReceiveNo = StringUtils.parseString(wReader.get("ReceiveNo"));
                wItem.ReceiveTime = StringUtils.parseCalendar(wReader.get("ReceiveTime"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.StatusText = WMSPickDemandStatusNew.getEnumType(wItem.Status).getLable();
                wItem.SendStatus = StringUtils.parseInt(wReader.get("SendStatus"));
                wItem.SendStatusText = WMSSendStatus.getEnumType(wItem.SendStatus).getLable();
                wItem.SendDesc = StringUtils.parseString(wReader.get("SendDesc"));
                wItem.CreateID = StringUtils.parseInt(wReader.get("CreateID"));
                wItem.Creator = StringUtils.parseString(wReader.get("Creator"));
                wItem.CreateTime = StringUtils.parseCalendar(wReader.get("CreateTime"));
                wItem.WMSDemandNo = StringUtils.parseString(wReader.get("WMSDemandNo"));
                wItem.Remark = StringUtils.parseString(wReader.get("Remark"));
                wItem.SourceType = StringUtils.parseInt(wReader.get("SourceType"));
                wItem.SourceID = StringUtils.parseInt(wReader.get("SourceID"));
                wItem.SourceCode = StringUtils.parseString(wReader.get("SourceCode"));
                wItem.ShortageIdentification = StringUtils.parseInt(wReader.get("ShortageIdentification"));
                wItem.OriginalOrderNo = StringUtils.parseString(wReader.get("OriginalOrderNo"));
                wItem.ParentDemandNo = StringUtils.parseString(wReader.get("ParentDemandNo"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 获取最新的编码
     */
    public synchronized String GetNewCode(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        String wResult = "";
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser, MESDBSource.Basic, 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format(
                    "SELECT DemandNo FROM {0}.wms_batchpickdemand WHERE id IN( SELECT MAX(ID) FROM {0}" +
                            ".wms_batchpickdemand);",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            int wNumber = 1;
            int wMonth = Calendar.getInstance().get(Calendar.MONTH) + 1;
            for (Map<String, Object> wReader : wQueryResult) {
                String wDemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                int wCodeMonth = StringUtils.parseInt(wDemandNo.substring(8, 10));
                if (wMonth > wCodeMonth)
                    wNumber = 1;
                else
                    wNumber = StringUtils.parseInt(wDemandNo.substring(10)) + 1;
            }

            wResult = StringUtils.Format("BSPD{0}{1}{2}", String.valueOf(Calendar.getInstance().get(Calendar.YEAR)),
                    String.format("%02d", Calendar.getInstance().get(Calendar.MONTH) + 1),
                    String.format("%04d", wNumber));
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public Map<Integer, Integer> GetPartAreaMap(BMSEmployee wLoginUser, OutResult<Integer> wErrorCode) {
        Map<Integer, Integer> wResult = new HashMap<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT WorkAreaID,StationID FROM {0}.lfs_workareastation WHERE ACTIVE=1;",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                int wWorkAreaID = StringUtils.parseInt(wReader.get("WorkAreaID"));
                int wStationID = StringUtils.parseInt(wReader.get("StationID"));
                if (!wResult.containsKey(wStationID)) {
                    wResult.put(wStationID, wWorkAreaID);
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    public List<WMSBatchPickDemand> SelectListByIDList(BMSEmployee wLoginUser, List<Integer> wTaskIDList,
                                                       OutResult<Integer> wErrorCode) {
        List<WMSBatchPickDemand> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wTaskIDList == null || wTaskIDList.size() <= 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.ProductNo,t2.Name LineName,t3.Name Sorter," +
                            "t3.LoginID SorterNo,t4.Name DeliveryMan,t4.LoginID DeliveryNo," +
                            "t5.Name ReceiveMan,t5.LoginID ReceiveNo,t6.Name Creator FROM {0}" +
                            ".wms_batchpickdemand t "
                            + " left join {0}.fpc_product t1 on t.ProductID=t1.ID "
                            + " left join {0}.fmc_line t2 on t.LineID=t2.ID "
                            + " left join {0}.mbs_user t3 on t.SorterID=t3.ID "
                            + " left join {0}.mbs_user t4 on t.DeliveryID=t4.ID "
                            + " left join {0}.mbs_user t5 on t.ReceiveID=t5.ID "
                            + " left join {0}.mbs_user t6 on t.CreateID=t6.ID "
                            + "WHERE  t.ID in ({1});",
                    wInstance.Result, StringUtils.Join(",", wTaskIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SetValue(wResultList, wQueryResult);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public List<WMSBatchPickDemand> SelectTaskDoneList(BMSEmployee wLoginUser, Calendar wStartTime, Calendar wEndTime
            , OutResult<Integer> wErrorCode) {
        List<WMSBatchPickDemand> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            Calendar wBaseTime = Calendar.getInstance();
            wBaseTime.set(2000, 0, 1, 0, 0, 0);
            if (wStartTime == null) {
                wStartTime = wBaseTime;
            }
            if (wEndTime == null) {
                wEndTime = wBaseTime;
            }
            if (wStartTime.compareTo(wEndTime) > 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.ProductNo,t2.Name LineName,t3.Name Sorter," +
                            "t3.LoginID SorterNo,t4.Name DeliveryMan,t4.LoginID DeliveryNo," +
                            "t5.Name ReceiveMan,t5.LoginID ReceiveNo,t6.Name Creator FROM {0}" +
                            ".wms_batchpickdemand t "
                            + " left join {0}.fpc_product t1 on t.ProductID=t1.ID "
                            + " left join {0}.fmc_line t2 on t.LineID=t2.ID "
                            + " left join {0}.mbs_user t3 on t.SorterID=t3.ID "
                            + " left join {0}.mbs_user t4 on t.DeliveryID=t4.ID "
                            + " left join {0}.mbs_user t5 on t.ReceiveID=t5.ID "
                            + " left join {0}.mbs_user t6 on t.CreateID=t6.ID "
                            + "WHERE (t.SorterID=:UserID or t.DeliveryID=:UserID or t.ReceiveID=:UserID) " +
                            "and ((t.CreateTime>:StartTime and t.CreateTime<:EndTime) " +
                            "or (t.SorterTime>:StartTime and t.SorterTime<:EndTime) " +
                            "or (t.ReceiveTime>:StartTime and t.ReceiveTime<:EndTime) " +
                            "or (t.DeliveryTime>:StartTime and t.DeliveryTime<:EndTime));",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("UserID", wLoginUser.ID);
            wParamMap.put("StartTime", wStartTime);
            wParamMap.put("EndTime", wEndTime);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SetValue(wResultList, wQueryResult);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public List<WMSBatchPickDemand> WMS_QueryBatchLineEdgeSource(BMSEmployee wLoginUser,
                                                                 WMSLineEdgeLibrary wWMSLineEdgeLibrary,
                                                                 OutResult<Integer> wErrorCode) {
        List<WMSBatchPickDemand> wResult = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            String wSQL = StringUtils.Format("SELECT DISTINCT t.ID,t2.DemandNo,t6.ProductNo,t7.Name,t.PickedNumber " +
                            "FROM {0}.`wms_batchpickdemanditemstock` t  " +
                            "LEFT JOIN {0}.`wms_batchpickdemanditem` t1 ON t.TaskID= t1.ID " +
                            "LEFT JOIN {0}.`wms_batchpickdemand` t2 ON t1.DemandID=t2.ID " +
                            "LEFT JOIN {0}.`sfc_batchbomtaskbpm` t3 ON t2.SourceID=t3.ID " +
                            "LEFT JOIN {0}.`sfc_batchbomtaskbpmitem` t4 ON t4.SFCBatchBOMTaskID=t3.ID " +
                            "LEFT JOIN {0}.lfs_workareastation t5 ON t4.PartID=t5.StationID " +
                            "LEFT JOIN {0}.fpc_product t6 ON t2.ProductID=t6.ID " +
                            "LEFT JOIN {0}.fmc_line t7 ON t2.LineID=t7.ID " +
                            "WHERE t.BatchNumber=:BatchNumber AND t1.MaterialID=:MaterialID AND t2.Status=4 AND t5" +
                            ".WorkAreaID=:WorkAreaID ORDER BY id ASC; ",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<String, Object>();

            wParamMap.put("BatchNumber", wWMSLineEdgeLibrary.BatchNumber);
            wParamMap.put("MaterialID", wWMSLineEdgeLibrary.MaterialID);
            wParamMap.put("WorkAreaID", wWMSLineEdgeLibrary.AreaID);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            List<WMSBatchPickDemand> wList = new ArrayList<>();
            for (Map<String, Object> wReader : wQueryResult) {
                WMSBatchPickDemand wItem = new WMSBatchPickDemand();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.ProductNo = StringUtils.parseString(wReader.get("ProductNo"));
                wItem.LineName = StringUtils.parseString(wReader.get("Name"));
                wItem.SubMaterialNumber = StringUtils.parseDouble(wReader.get("PickedNumber"));

                wList.add(wItem);
            }

            //按照单号去重
            List<WMSBatchPickDemand> wItemList = wList.stream()
                    .collect(Collectors.collectingAndThen(Collectors.toCollection(
                                    () -> new TreeSet<>(Comparator.comparing(WMSBatchPickDemand::getDemandNo))),
                            ArrayList::new));
            for (WMSBatchPickDemand wWMSBatchPickDemand : wItemList) {
                wWMSBatchPickDemand.SubMaterialNumber =
                        wList.stream().filter(p -> p.DemandNo.equals(wWMSBatchPickDemand.DemandNo)).mapToDouble(p -> p.SubMaterialNumber).sum();
                wWMSBatchPickDemand.SubMaterialNumber = new BigDecimal(wWMSBatchPickDemand.SubMaterialNumber)
                        .setScale(2, RoundingMode.HALF_UP).doubleValue();
                wResult.add(wWMSBatchPickDemand);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }
}
